package com.apes.fn.business.fixedLoss.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.business.fixedLoss.repository.BusinessControlSql;
import com.apes.framework.api.DomainService;
import com.apes.framework.plugin.esb.api.SimpleRequest;
import com.apes.framework.util.Arith;
import com.apes.framework.util.DateUtil;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.DataFormatException;

/**
 * @description: 商务总控相关业务
 * @author: wsj
 * @time: 2021/3/10 16:37
 */
@Service("businessControl")
public class BusinessControl extends DomainService {
    @Autowired
    private BusinessControlSql sql;
    @Autowired
    private EntityManager entityManager;

    public JSONArray inLossQuotation () {
        JSONArray array = sql.inLossQuotation();
        return getResultArray(array);
    }
    public JSONArray trackData () {
        JSONArray array = sql.trackData();
        return  getResultArray(array);
    }

    private JSONArray getResultArray(JSONArray array) {
        JSONArray resultArray = new JSONArray();
        if (array.size() > 0) {
            array.stream().filter(
                    item -> {
                        JSONObject object = (JSONObject) item;
                        return object.getDoubleValue("QUANTITY") > 0;
                    }
            ).forEach(
                    item -> {
                        JSONObject object = (JSONObject) item;
                        JSONObject data = new JSONObject();
                        data.put("LEFTTEXT", object.getString("NAME"));
                        data.put("RIGHTTEXT", object.getString("QUANTITY"));
                        data.put("TYPE", object.getString("TYPE"));
                        resultArray.add(data);
                    }
            );
        }
        return resultArray;
    }


    /**
     * 定损中数据详情
     */
    public JSONObject inLossQuotationForDetail(SimpleRequest re){
        JSONObject parameters = parseParameters(re);
        JSONObject object = new JSONObject();
        if (StringUtils.isEmpty(parameters.getString("TYPE"))) {
            parameters.put("TYPE", 1);
        }
        //取结果集
        List<Map<String, Object>> resultList = findInLosQuotation(parameters.getIntValue("TYPE"), parameters.getInteger("page"), parameters.getInteger("size"));
        Object costsCount = findInLossQuotationCount(parameters.getIntValue("TYPE"));
        object.put("content", resultList);
        object.put("total", costsCount);
        object.put("pages", costsCount);
        return object;
    }

    /**
     * 报案跟踪数据详情
     * @param re
     * @return
     */
    public JSONObject trackDataDetail(SimpleRequest re){
        JSONObject parameters = parseParameters(re);
        JSONObject object = new JSONObject();
        if (StringUtils.isEmpty(parameters.getString("TYPE"))) {
            parameters.put("TYPE", 1);
        }
        //取结果集
        List<Map<String, Object>> resultList = findTrackDataDetail(parameters.getIntValue("TYPE"), parameters.getInteger("page"), parameters.getInteger("size"));
        Object costsCount = findReportTrackCount(parameters.getIntValue("TYPE"));
        object.put("content", resultList);
        object.put("total", costsCount);
        object.put("pages", costsCount);
        return object;
    }

    /**
     * 解析前端传来的参数
     *
     */
    private JSONObject parseParameters(SimpleRequest request){
        JSONObject requestObject = request.getJO();
        JSONObject result = new JSONObject();
        JSONObject data = requestObject.getJSONObject("data");
        result.put("TYPE", data.getString("TYPE"));
        result.put("page", requestObject.get("page"));
        result.put("size", requestObject.get("size"));

        return result;
    }


    /**
     * 定损中的总页数
     *
     */
    public Object findInLossQuotationCount(int type) {
        String querySql = "SELECT COUNT(1) COUNT FROM ( " ;
        if (type ==1) {
            querySql += sql.getWaitQuotationSql();
        }else if (type == 2) {
            querySql += sql.getUnconfirmedSql();
        }
        querySql += " ) ";
        Query query = entityManager.createNativeQuery(querySql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 报案跟踪的总页数
     *
     */
    public Object findReportTrackCount(int type) {
        String querySql = "SELECT COUNT(1) COUNT FROM ( " ;
        if (type ==1) {
            querySql += sql.getBackstageTrackSql();
        }else if (type == 2) {
            querySql += sql.getStoreTrackSql();
        }
        querySql += " ) ";
        Query query = entityManager.createNativeQuery(querySql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }
    /**
     * 查找定损中
     *
     */
    public List<Map<String, Object>> findInLosQuotation(int type, int page, int size) {
        String querySql = "SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( ";
        //type(1:待报价,  2:未确认)
        if (type ==1) {
            querySql += sql.getWaitQuotationSql();
        }else if (type == 2) {
            querySql += sql.getUnconfirmedSql();
        }
        querySql += " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return queryForData(querySql, page, size);
    }

    /**
     * 查找报案跟踪数据
     *
     */
    private List<Map<String, Object>> findTrackDataDetail(int type, int page, int size) {
        String querySql = "SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( ";
        //type(1:后台跟踪,  2:门店跟踪)
        if (type ==1) {
            querySql += sql.getBackstageTrackSql();
        }else if (type == 2) {
            querySql += sql.getStoreTrackSql();
        }
        querySql += " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return queryForData(querySql, page, size);
    }

    private List<Map<String, Object>> queryForData (String querySql,int page,int size) {
        Query query = entityManager.createNativeQuery(querySql);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 车险销售分析表格
     * @param request
     * @return
     * @throws ParseException
     */
    public List  insuranceSaleFrom (SimpleRequest request) throws ParseException {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSaleData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double saleAmount = object.getDoubleValue("SALE_AMOUNT");
                    computeProportion(object, saleAmount);
                    object.remove("SUPPLIER");
                }
        );
        return new ArrayList<>(array);
    }

    /**
     * 车险销售分析饼图
     * @param request
     * @return
     * @throws ParseException
     */
    public List  insuranceSalePieChart (SimpleRequest request) throws ParseException {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSaleData(param);
        return  array.stream().map(
                item -> {
                    JSONObject data = (JSONObject) item;
                    JSONObject object = new JSONObject();
                    object.put("AMOUNT", data.getDoubleValue("SALE_AMOUNT"));
                    object.put("CLASS_NAME", data.getString("ABBREVIATION"));
                    object.put("SYS_RID", 1);
                    object.put("percent", 0);
                    return  object;
                }
        ).collect(Collectors.toList());
    }

    /**
     * 车险销售分析柱形图
     * @param request
     * @return
     * @throws ParseException
     */
    public List  insuranceSaleBarChart (SimpleRequest request) throws ParseException {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSaleData(param);
        return  array.stream().map(
                item -> {
                    JSONObject data = (JSONObject) item;
                    JSONObject object = new JSONObject();
                    object.put("COMMERCIAL_INSURANCE", data.getDoubleValue("COMMERCIAL_INSURANCE"));
                    object.put("INSURANCE_COMPANY", data.getString("ABBREVIATION"));
                    object.put("COMPULSORY_INSURANCE", data.getDoubleValue("COMPULSORY_INSURANCE"));
                    return  object;
                }
        ).collect(Collectors.toList());
    }

    /**
     * 定损报表分析柱形图
     * @param request
     * @return
     * @throws ParseException
     */
    public List  insuranceReportBarChart (SimpleRequest request) throws Exception {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START")) || StringUtils.isEmpty(param.getString("DATE_END"))) {
            throw new RuntimeException("查询条件时间段不能为空");
        }

        // JSONArray monthArray = computeMonth(DateUtil.parseUTC(param.getString("DATE_START")),
        //         DateUtil.parseUTC(param.getString("DATE_END")));
        JSONArray array = sql.queryAutoInsuranceReportData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double solve = object.getDoubleValue("SETTLEMENT");
                    computeProportion(object, solve);
                }
        );
        //
        // JSONArray resultArray = new JSONArray();
        //
        // monthArray.forEach(
        //         item -> {
        //             JSONObject object = (JSONObject) item;
        //             String month = object.getString("month");
        //             JSONObject monthData = new JSONObject();
        //             monthData.put("date", month);
        //             monthData.put("company", new JSONArray());
        //             array.stream().collect(Collectors.groupingBy(d -> {
        //                 JSONObject group = (JSONObject) d;
        //                 return group.getString("STATISTIC_DATE");
        //             })).forEach((key,values) -> {
        //                 if (Objects.equals(month,key)) {
        //                     monthData.put("company", values);
        //                 }
        //             });
        //             resultArray.add(monthData);
        //         }
        // );
        return new ArrayList<>(array);
    }
    /**
     * 定损报表分析表格
     * @param request
     * @return
     * @throws ParseException
     */
    public List  insuranceReportFrom (SimpleRequest request) throws Exception {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryAutoInsuranceReportData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double solve = object.getDoubleValue("SETTLEMENT");
                    double total = object.getDoubleValue("TOTAL");
                    object.put("claimRate", total == 0 ? 100 : Arith.round(Arith.div(solve, total, 4) * 100,2));
                    object.remove("LAST_MONTH");
                    object.remove("LAST_YEAR");
                }
        );
        return new ArrayList<>(array);
    }

    /**
     * 计算同比,环比
     * @param object 单条数据对象
     * @param solve  当前总数
     */
    private void computeProportion(JSONObject object, double solve) {
        double lastMonth = object.getDoubleValue("LAST_MONTH");
        double lastYear = object.getDoubleValue("LAST_YEAR");
        object.put("YearOnYear", lastYear == 0 ? 100 : Arith.round(Arith.div(solve - lastYear, lastYear, 4) * 100,2));
        object.put("ChainRatio", lastMonth == 0 ? 100 : Arith.round(Arith.div(solve - lastMonth, lastMonth, 4) * 100,2));
        object.remove("LAST_MONTH");
        object.remove("LAST_YEAR");
    }

    /**
     * 计算时间段内所有月
     */
    private JSONArray computeMonth(Date startDate, Date endDate) throws Exception {
        JSONArray array = new JSONArray();
        //首先将传参默认为每个月第一天,以免漏数据
        startDate = DateUtil.getMonthFirstDay(startDate);
        endDate = DateUtil.getMonthFirstDay(endDate);
        if (startDate.after(endDate)) {
            throw new DataFormatException("开始时间不能大于结束时间");
        }
        while(true){
            JSONObject object = new JSONObject();
            object.put("month", DateUtil.format(startDate, "YYYY-MM"));
            if (startDate.after(endDate)) {
                break;
            }else {
                array.add(object);
                startDate = DateUtil.add(startDate,Calendar.MONTH,1);
            }
        }
        return array;
    }

    /**
     * 定损商务审核效率
     * @param request 请求数据
     * @return 结果集
     */
    public List  queryBusinessApproveData (SimpleRequest request) {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryBusinessApproveData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double orderNumber = object.getDoubleValue("ORDER_NUMBER");
                    double consumeTime = object.getDoubleValue("CONSUME_TIME");
                    object.put("claimRate", orderNumber == 0 ? 0 : Arith.div(consumeTime, orderNumber, 2) );
                    object.remove("CONSUME_TIME");
                }
        );
        return new ArrayList<>(array);
    }

    /**
     * 定损理赔统计.饼图
     * @param request 请求数据
     * @return 结果集
     */
    public List  settlementClaimPieChart (SimpleRequest request) {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSettlementClaimData(param);
        return  array.stream().map(
                item -> {
                    JSONObject data = (JSONObject) item;
                    JSONObject object = new JSONObject();
                    object.put("AMOUNT", data.getDoubleValue("FIXED_LOSS_AMOUNT"));
                    object.put("CLASS_NAME", data.getString("NAME"));
                    // object.put("SYS_RID", 1);
                    // object.put("percent", 0);
                    return  object;
                }
        ).collect(Collectors.toList());
    }

    /**
     * 定损理赔统计.柱形图
     * @param request 请求数据
     * @return 结果集
     */
    public List  settlementClaimBarChart (SimpleRequest request) {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSettlementClaimData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double saleAmount = object.getDoubleValue("FIXED_LOSS_AMOUNT");
                    computeProportion(object, saleAmount);
                    object.remove("LAST_YEAR");
                    object.remove("LAST_MONTH");
                    object.remove("ID");
                    object.remove("TOTAL_AMOUNT");
                }
        );
        return new ArrayList<>(array);
    }

    /**
     * 定损理赔统计.表格
     * @param request 请求数据
     * @return 结果集
     */
    public List  settlementClaimFrom (SimpleRequest request) {
        JSONObject param = request.getJO();
        if (StringUtils.isEmpty(param.getString("DATE_START"))) {
            throw new RuntimeException("查询条件开始时间不能为空");
        }
        JSONArray array = sql.queryInsuranceSettlementClaimData(param);
        array.forEach(
                item ->{
                    JSONObject object = (JSONObject) item;
                    double saleAmount = object.getDoubleValue("FIXED_LOSS_AMOUNT");
                    computeProportion(object, saleAmount);
                    double totalAmount = object.getDoubleValue("TOTAL_AMOUNT");
                    object.put("AmountRatio", totalAmount == 0 ? 100 : Arith.round(Arith.div(saleAmount, totalAmount, 4) * 100,2));
                    object.remove("LAST_YEAR");
                    object.remove("LAST_MONTH");
                    object.remove("ID");
                    object.remove("TOTAL_AMOUNT");
                }
        );
        return new ArrayList<>(array);
    }

    public JSONArray unsettled () {
        JSONArray array = sql.unsettledData();
        return getResultArray(array);
    }

    /**
     * 未结算单据数据详情
     * @param re
     * @return
     */
    public JSONObject unsettledDetail(SimpleRequest re){
        JSONObject object = new JSONObject();
        JSONArray array = sql.unsettledDetailData();
        object.put("content", array);
        return object;
    }
}
